from sqlalchemy import create_engine, Column, Integer, BigInteger,Boolean,String,DateTime,func,and_,text
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import sessionmaker
from datetime import datetime,timedelta
from model.FileInf import FileInf

# 创建数据库连接引擎
engine = create_engine('mysql+pymysql://root:123456@localhost/up6', echo=True)

# 创建基类
Base = declarative_base()

# 定义Users模型
class Up6File(Base):
    __tablename__ = 'up6_files'
    
    f_id = Column(String(32), primary_key=True)
    f_pid = Column(String(32),default='')
    f_pidRoot = Column(String(32),default='')
    f_fdTask = Column(Boolean,default=False)
    f_fdChild = Column(Boolean,default=False)
    f_uid = Column(String(32),default='')
    f_nameLoc = Column(String(255),default='')
    f_nameSvr = Column(String(255),default='')
    f_pathLoc = Column(String(512),default='')
    f_pathSvr = Column(String(512),default='')
    f_pathRel = Column(String(512),default='')
    f_md5 = Column(String(40),default='')
    f_lenLoc = Column(BigInteger,default=0)
    f_sizeLoc = Column(String(10),default='0%')
    f_pos = Column(BigInteger,default=0)
    f_lenSvr = Column(BigInteger,default=0)
    f_perSvr = Column(String(7),default='0%')
    f_complete = Column(Boolean,default=False)
    f_time = Column(DateTime,default=datetime.now)
    f_deleted = Column(Boolean,default=False)
    f_scan = Column(Boolean,default=False)
    f_lenLocSec = Column(BigInteger,default=0)
    f_encrypt = Column(Boolean,default=False)
    f_encryptAgo = Column(String(7),default='')
    f_blockSize = Column(BigInteger,default=0)
    f_blockSizeSec = Column(BigInteger,default=0)
    f_object_key = Column(String(512),default='')

    def __repr__(self):
        return f"<up6_files(id={self.f_id}, name='{self.f_nameLoc}', url='{self.f_object_url}')>"
    
    def toDic(self):
        dic={}
        dic['id']=self.f_id
        dic['pid']=self.f_pid
        dic['pidRoot']=self.f_pidRoot
        dic['fdTask']=self.f_fdTask
        dic['fdChild']=self.f_fdChild
        dic['uid']=self.f_uid
        dic['nameLoc']=self.f_nameLoc
        dic['nameSvr']=self.f_nameSvr
        dic['pathLoc']=self.f_pathLoc
        dic['pathSvr']=self.f_pathSvr
        dic['pathRel']=self.f_pathRel
        dic['md5']=self.f_md5
        dic['lenLoc']=self.f_lenLoc
        dic['sizeLoc']=self.f_sizeLoc
        dic['pos']=self.f_pos
        dic['lenSvr']=self.f_lenSvr
        dic['perSvr']=self.f_perSvr
        dic['complete']=self.f_complete
        dic['time']=self.f_time.strftime("%Y-%m-%d %H:%M:%S")
        dic['deleted']=self.f_deleted
        dic['scan']=self.f_scan
        dic['lenLocSec']=self.f_lenLocSec
        dic['encrypt']=self.f_encrypt
        dic['encryptAgo']=self.f_encryptAgo
        dic['blockSize']=self.f_blockSize
        dic['blockSizeSec']=self.f_blockSizeSec
        dic['object_key']=self.f_object_key
        
        return dic

# 创建所有表
Base.metadata.create_all(engine)

# 创建会话
Session = sessionmaker(bind=engine)
session = Session()

# 增加数据
def add(f):
    obj = Up6File(f_id=f.id,
        f_pid=f.pid,
        f_pidRoot=f.pidRoot,
        f_fdTask=f.fdTask,
        f_fdChild=f.fdChild,
        f_uid=f.uid,
        f_nameLoc=f.nameLoc,
        f_nameSvr=f.nameSvr,
        f_pathLoc=f.pathLoc,
        f_pathSvr=f.pathSvr,
        f_pathRel=f.pathRel,
        f_md5=f.md5,
        f_lenLoc=f.lenLoc,
        f_sizeLoc=f.sizeLoc,
        f_pos=f.pos,
        f_lenSvr=f.lenSvr,
        f_perSvr=f.perSvr,
        f_complete=f.complete,
        f_time=f.time,
        f_deleted=f.deleted,
        f_scan=f.scan,
        f_lenLocSec=f.lenLocSec,
        f_encrypt=f.encrypt,
        f_encryptAgo=f.encryptAgo,
        f_blockSize=f.blockSize,
        f_blockSizeSec=f.blockSizeSec,
        f_object_key=f.object_key
        )
    session.add(obj)
    session.commit()

#查询标题
def existMD5(md5):
    ret = session.query(Up6File).filter(Up6File.f_md5 == md5).first()
    return ret

#更新完成标识
def complete(id):
    ret = session.query(Up6File).get(id)
    if not ret:
        ret.f_complete=True
        session.commit()
    return ret

# 更新文件上传进度
def process(uid,id,offset,lenSvr,perSvr):
    ret = session.query(Up6File).get(id)
    if not ret:
        ret.f_pos=offset
        ret.f_lenSvr=lenSvr
        ret.f_perSvr=perSvr
        session.commit()
    return ret

# 查询数据
def all():
    arr=[]
    ts = session.query(Up6File).all()
    if ts:
        for t in ts:
            print(t)
            arr.append(t.toDic())
    return arr

def all_complete():
    arr=[]
    ts = session.query(Up6File).filter(Up6File.f_complete==True,Up6File.f_deleted==False)

    for t in ts:
        arr.append(t.toDic())
    return arr

# 加载所有未完成列表
def uncmps():
    arr=[]
    ts = session.query(Up6File).filter(Up6File.f_complete==False,Up6File.f_deleted==False)
    for t in ts:
        arr.append(t.toDic())
    return arr

#统计总文章数
def count():
    ct = session.query(Up6File).count()
    return ct


# 更新数据
def update(id, name=None, url=None):
    user = session.query(Up6File).get(id)
    if user:
        if age is not None:
            user.age = age
        if name is not None:
            user.name = name
        session.commit()
        return user
    return None

# 删除数据
def delete(id,uid):
    user = session.query(Up6File).filter(Up6File.f_id==id,Up6File.f_uid==uid).first()
    if user:
        session.delete(user)
        session.commit()
        return True
    return False

# 清除 up6_files,up6_folders数据
def clear():
    session.execute(text("TRUNCATE  TABLE up6_files"))
    session.execute(text("TRUNCATE  TABLE up6_folders"))
    session.commit()